
package model;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.mybeans.dao.DAOException;
import org.mybeans.factory.MatchArg;
import org.mybeans.factory.RollbackException;

import databeans.Transaction;


public class TransactionDAOSQL {
    
    private List<Connection> connectionPool = new ArrayList<Connection>();

    private String jdbcDriver;
    private String jdbcURL;
    private String jdbcUser;
    private String jdbcPass;
    
    public TransactionDAOSQL(String jdbcDriver, String jdbcURL, String jdbcUser, String jdbcPass) {
        this.jdbcDriver = jdbcDriver;
        this.jdbcURL    = jdbcURL;
        this.jdbcUser    = jdbcUser;
        this.jdbcPass    = jdbcPass;
    }
    
    private synchronized Connection getConnection() throws DAOException {
        if (connectionPool.size() > 0) {
            return connectionPool.remove(connectionPool.size()-1);
        }
        
        try {
            Class.forName(jdbcDriver);
        } catch (ClassNotFoundException e) {
            throw new DAOException(e);
        }

        try {
            return DriverManager.getConnection(jdbcURL,jdbcUser,jdbcPass);
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }
    
    private synchronized void releaseConnection(Connection con) {
        connectionPool.add(con);
    }

	public databeans.Transaction[] getUndoneTransactions() throws DAOException {
		Connection con = null;
		List<Transaction> list = new ArrayList<Transaction>();
		try {       	
			
			con = getConnection();

        	PreparedStatement pstmt = con.prepareStatement("SELECT * FROM cfa_transaction WHERE ISNULL(`executeDate`)");
        	//pstmt.setString(1,null);
        	ResultSet rs = pstmt.executeQuery();
        	 
        	Transaction tempT;
            while (rs.next()) {
            	tempT = new Transaction(rs.getInt("transactionID"),rs.getInt("customerID"),rs.getInt("fundID"),rs.getDate("executeDate"),rs.getDate("submissionDate"),rs.getLong("shares"),rs.getString("transactionType"),rs.getLong("amount"),rs.getInt("correspondingTransactionID"),rs.getInt("success"));
            	list.add(tempT);
            }
        	
        	rs.close();
        	pstmt.close();
        	releaseConnection(con);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		return list.toArray(new Transaction[list.size()]);
	}

	public databeans.Transaction getMatchingTransaction(int id) throws DAOException {
		Connection con = null;
		Transaction tempT;
		try {       	
			
			con = getConnection();

        	PreparedStatement pstmt = con.prepareStatement("SELECT * FROM cfa_transaction WHERE transactionID=?");
        	pstmt.setInt(1,id);
        	ResultSet rs = pstmt.executeQuery();
        	 
        	if (!rs.next()) {
        		tempT = null;
        	} else {
        		tempT = new Transaction(rs.getInt("transactionID"),rs.getInt("customerID"),rs.getInt("fundID"),rs.getDate("executeDate"),rs.getDate("submissionDate"),rs.getLong("shares"),rs.getString("transactionType"),rs.getLong("amount"),rs.getInt("correspondingTransactionID"),rs.getInt("success"));
        	}
        	
        	rs.close();
        	pstmt.close();
        	releaseConnection(con);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		return tempT;
	}
	
	public ArrayList<Transaction> get5LastestTransactions(int customerID) throws DAOException {
		ArrayList<Transaction> transactions = new ArrayList<Transaction>(); 
		
		Connection con = null; 
		try {       	
			
			con = getConnection();

        	PreparedStatement pstmt = con.prepareStatement("SELECT * FROM cfa_transaction WHERE customerID=? Order by submissionDate");
        	pstmt.setInt(1,customerID);
        	ResultSet rs = pstmt.executeQuery();
        	
        	Transaction tempT;
        	int i = 5; 
            while (rs.next()&&(i>=1)) {
            	tempT = new Transaction(rs.getInt("transactionID"),rs.getInt("customerID"),rs.getInt("fundID"),rs.getDate("executeDate"),rs.getDate("submissionDate"),rs.getLong("shares"),rs.getString("transactionType"),rs.getLong("amount"),rs.getInt("correspondingTransactionID"),rs.getInt("success"));
            	transactions.add(tempT);
            	i = i-1; 
            }
        	
        	rs.close();
        	pstmt.close();
        	releaseConnection(con);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		
		System.out.println("TransactionDAOSQL + get5LatestTransactions()+transactionSize: "+transactions.size()); 
		return transactions;
	}
	
	public ArrayList<Transaction> getLastBuyTransactions(int customerID) throws DAOException {
		ArrayList<Transaction> transactions = new ArrayList<Transaction>(); 
		
		Connection con = null; 
		try {       	
			
			con = getConnection();

        	PreparedStatement pstmt = con.prepareStatement("SELECT * FROM cfa_transaction WHERE customerID=? AND transactionType='buyOrder' Order by submissionDate");
        	pstmt.setInt(1,customerID);
        	ResultSet rs = pstmt.executeQuery();
        	
        	Transaction tempT;
        	int i = 5; // number of transactions to return
        	
            while (rs.next()&&(i>=1)) {
            	tempT = new Transaction(rs.getInt("transactionID"),rs.getInt("customerID"),rs.getInt("fundID"),rs.getDate("executeDate"),rs.getDate("submissionDate"),rs.getLong("shares"),rs.getString("transactionType"),rs.getLong("amount"),rs.getInt("correspondingTransactionID"),rs.getInt("success"));
            	transactions.add(tempT);
            	i = i-1; 
            }
        	
        	rs.close();
        	pstmt.close();
        	releaseConnection(con);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		
		System.out.println("TransactionDAOSQL + get5LatestTransactions()+transactionSize: "+transactions.size()); 
		return transactions;
	}
	
	public ArrayList<Transaction> getLastSellTransactions(int customerID) throws DAOException {
		ArrayList<Transaction> transactions = new ArrayList<Transaction>(); 
		
		Connection con = null; 
		try {       	
			
			con = getConnection();

        	PreparedStatement pstmt = con.prepareStatement("SELECT * FROM cfa_transaction WHERE customerID=? AND transactionType='sellOrder' Order by submissionDate");
        	pstmt.setInt(1,customerID);
        	ResultSet rs = pstmt.executeQuery();
        	
        	Transaction tempT;
        	int i = 5; // number of transactions to return
        	
            while (rs.next()&&(i>=1)) {
            	tempT = new Transaction(rs.getInt("transactionID"),rs.getInt("customerID"),rs.getInt("fundID"),rs.getDate("executeDate"),rs.getDate("submissionDate"),rs.getLong("shares"),rs.getString("transactionType"),rs.getLong("amount"),rs.getInt("correspondingTransactionID"),rs.getInt("success"));
            	transactions.add(tempT);
            	i = i-1; 
            }
        	
        	rs.close();
        	pstmt.close();
        	releaseConnection(con);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		
		System.out.println("TransactionDAOSQL + get5LatestTransactions()+transactionSize: "+transactions.size()); 
		return transactions;
	}
}
